package com.probiz.estore.product.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.probiz.estore.common.model.product.ProductReview;
import com.probiz.estore.common.util.SearchUtil;
import com.probiz.estore.core.dao.impl.HibernateGenericDaoImpl;
import com.probiz.estore.core.search.SearchCriteria;
import com.probiz.estore.product.dao.ProductReviewDao;
import com.probiz.estore.product.model.ProductReviewSearchCriteria;

/**
 * Dao implementation for ProductReview.
*/
public class ProductReviewDaoImpl extends HibernateGenericDaoImpl<ProductReview> implements ProductReviewDao {
	

	
	protected void initFilterHqlMap() {
		filterHqlMap.put(ProductReview.FilterHqlName.ELIGIBLE_PRODUCT_LIST, "select re.product from ReviewEligibility re where re.customer.appuserId=? and re.product.status=1 and re.remainedCount>=1 and re.expiredTime > ? order by re.createTime desc");
		filterHqlMap.put(ProductReview.FilterHqlName.MY_REVIEW_LIST, "from ProductReview pr where pr.customer.appuserId=? and pr.parent is null order by pr.createTime desc");
		filterHqlMap.put(ProductReview.FilterHqlName.MY_REVIEW_REPLY_LIST, "select distinct(pr.parent) from ProductReview pr where  pr.customer.appuserId=? and pr.parent is not null order by pr.parent.createTime desc");
		filterHqlMap.put(ProductReview.FilterHqlName.MY_VOTED_REVIEW_LIST, "select rv.productReview from ReviewVote rv where rv.customer.appuserId=? and rv.chosen =1 order by rv.createTime desc");
	}
	
	
	/*
	 * (non-Javadoc)
	 * @see com.probiz.estore.product.dao.ProductReviewDao#getCountsByScoreLevelAndProductId(java.lang.Integer)
	 */
	public Integer[] getCountsByScoreLevelAndProductId(Integer productId){
		int allCount= this.countByHql("from ProductReview pr where pr.product.productId=? and pr.parent is null and pr.status=1", productId).intValue();
		int positiveCount= this.countByHql("from ProductReview pr where pr.product.productId=? and pr.parent is null and pr.status=1 and pr.score>=4 and pr.score<=5", productId).intValue();
		int neutralCount = this.countByHql("from ProductReview pr where pr.product.productId=? and pr.parent is null and pr.status=1 and pr.score>=3 and pr.score<4", productId).intValue();
		int negativeCount = this.countByHql("from ProductReview pr where pr.product.productId=? and pr.parent is null and pr.status=1 and pr.score<3", productId).intValue();
		Integer[] counts = new Integer[4];
		counts[0] = Integer.valueOf(allCount);
		counts[1] = Integer.valueOf(positiveCount);
		counts[2] = Integer.valueOf(neutralCount);
		counts[3] = Integer.valueOf(negativeCount);
		return counts;
		
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.probiz.estore.product.dao.ProductReviewDao#getCountsByScoreSpanAndProductId(java.lang.Integer)
	 */
	public Integer[] getCountsByScoreSpanAndProductId(Integer productId){
		int allCount= this.countByHql("from ProductReview pr where pr.product.productId=? and pr.parent is null and pr.status=1", productId).intValue();
		int oneStarCount= this.countByHql("from ProductReview pr where pr.product.productId=? and pr.parent is null and pr.status=1 and pr.score>=0 and pr.score<2", productId).intValue();
		int twoStarCount= this.countByHql("from ProductReview pr where pr.product.productId=? and pr.parent is null and pr.status=1 and pr.score>=2 and pr.score<3", productId).intValue();
		int threeStarCount= this.countByHql("from ProductReview pr where pr.product.productId=? and pr.parent is null and pr.status=1 and pr.score>=3 and pr.score<4", productId).intValue();
		int fourStarCount= this.countByHql("from ProductReview pr where pr.product.productId=? and pr.parent is null and pr.status=1 and pr.score>=4 and pr.score<5", productId).intValue();
		int fiveStarCount= this.countByHql("from ProductReview pr where pr.product.productId=? and pr.parent is null and pr.status=1 and pr.score>=5", productId).intValue();
		Integer[] counts = new Integer[6];
		counts[0] = Integer.valueOf(allCount);
		counts[1] = Integer.valueOf(oneStarCount);
		counts[2] = Integer.valueOf(twoStarCount);
		counts[3] = Integer.valueOf(threeStarCount);
		counts[4] = Integer.valueOf(fourStarCount);
		counts[5] = Integer.valueOf(fiveStarCount);
		return counts;
	}
	/*
	 * (non-Javadoc)
	 * @see com.probiz.estore.product.dao.ProductReviewDao#getCountsOfCustomer(java.lang.Integer)
	 */
	public Integer[] getCountsOfCustomer(Integer customerId){
		int eligibleProductListCount = this.countByFilter(ProductReview.FilterHqlName.ELIGIBLE_PRODUCT_LIST, customerId,new Date()).intValue();
		int myReviewListCount = this.countByFilter(ProductReview.FilterHqlName.MY_REVIEW_LIST, customerId).intValue();
		int myReviewReplyListCount = this.countByFilter(ProductReview.FilterHqlName.MY_REVIEW_REPLY_LIST, customerId).intValue();
		int myVotedReviewListCount = this.countByFilter(ProductReview.FilterHqlName.MY_VOTED_REVIEW_LIST, customerId).intValue();
		Integer[] counts = new Integer[4];
		counts[0] = Integer.valueOf(eligibleProductListCount);
		counts[1] = Integer.valueOf(myReviewListCount);
		counts[2] = Integer.valueOf(myReviewReplyListCount);
		counts[3] = Integer.valueOf(myVotedReviewListCount);
		return counts;
	}
	/*
	 * (non-Javadoc)
	 * @see com.probiz.estore.product.dao.ProductReviewDao#getSearchCriteria(com.probiz.estore.product.model.ProductReviewSearchCriteria, com.probiz.estore.core.search.SearchCriteria)
	 */
	public SearchCriteria getSearchCriteria(ProductReviewSearchCriteria productReviewSearchCriteria, SearchCriteria searchCriteria){
	   StringBuffer hqlsb = new StringBuffer(100);
       List<Object> paramList = new ArrayList<Object>();
       //start:构建HQL语句
     //特别注意，因为customer可为空，所以在查询评论人名称时候必须加上左外连接。
       hqlsb.append("select r from ProductReview r left join r.customer as u where 1=1 ");
      
       //-begin:*********回复的评论id，该属性非常重要*********
       if (null != productReviewSearchCriteria.getParentId()) {
    	   hqlsb.append(" and  r.parent.productReviewId = ? ");
    	   paramList.add(productReviewSearchCriteria.getParentId());
       }else{
    	   hqlsb.append(" and  r.parent is null ");
       }
       //-end:
       //-begin:商品id
       if (null != productReviewSearchCriteria.getProductId()) {
    	   hqlsb.append(" and  r.product.productId = ? ");
    	   paramList.add( productReviewSearchCriteria.getProductId());
       }
       //-end:商品id
       //-begin:评论标题
       if (StringUtils.isNotBlank(productReviewSearchCriteria.getSubject())) {
           hqlsb.append(" and  r.subject like ? ");
           paramList.add("%" + productReviewSearchCriteria.getSubject() + "%");
       }
       //-end:评论标题
       
       //-begin:商品关键词
       if (StringUtils.isNotBlank(productReviewSearchCriteria.getKeywords())) {
           String keywords = SearchUtil.getSqlQueryValue(productReviewSearchCriteria.getKeywords());
           hqlsb.append(" and (");
           hqlsb.append("    r.product.productName like ? ");
           hqlsb.append(" or r.product.keywords like ? ");
           hqlsb.append(" or r.product.productCode like ? ");
           hqlsb.append(" or r.product.category.categoryName like ? ");
           hqlsb.append(" or r.product.category.keywords like ? ");
           hqlsb.append(" )");
           for (int i = 0; i < 5; i++) {
               paramList.add("%" + keywords + "%");
           }
       }
       //-end:商品关键词
       //-start:商品编码
       if (StringUtils.isNotBlank(productReviewSearchCriteria.getProductCode())) {
           hqlsb.append(" and r.product.productCode = ?");
           paramList.add(productReviewSearchCriteria.getProductCode());
       }
       //-end:商品编码
       //-start:顾客名称
       if (StringUtils.isNotBlank(productReviewSearchCriteria.getReviewUserName())) {
    	   hqlsb.append(" and ( (r.customerName like ?)");
    	   hqlsb.append(" or (u is not null and u.firstname like ?)");
    	   hqlsb.append(" or (u is not null and u.username like ?) )");
    	   for (int i = 0; i < 3; i++) {
    		   paramList.add("%" + productReviewSearchCriteria.getReviewUserName() + "%");
    	   }	
       }
       //-end:顾客名称
       //-start:状态:显示/隐藏
       if (null != productReviewSearchCriteria.getStatus()) {
           hqlsb.append(" and r.status = ?");
           paramList.add(productReviewSearchCriteria.getStatus());
       }
       //-end:状态:显示/隐藏
       //-start:状态:已读/未读
       if (null != productReviewSearchCriteria.getIsRead()) {
    	   hqlsb.append(" and r.isRead = ?");
    	   paramList.add(productReviewSearchCriteria.getIsRead());
       }
       //-end:状态:已读/未读
       //-start:状态:置顶/非置顶
       if (null != productReviewSearchCriteria.getSortOrder()) {
    	   hqlsb.append(" and r.sortOrder = ?");
    	   paramList.add(productReviewSearchCriteria.getSortOrder());
       }
       //-end:状态:置顶/非置顶
       //-start:最低分
       if (null != productReviewSearchCriteria.getLowestScore()) {
           hqlsb.append(" and r.score >= ?");
           paramList.add(productReviewSearchCriteria.getLowestScore());
       }
       //-end:最低分
       //-start:最高分
       if (null != productReviewSearchCriteria.getHighestScore()) {
           hqlsb.append(" and r.score <= ?");
           paramList.add(productReviewSearchCriteria.getHighestScore());
       }
       //-end:最高分
       
       //-start:分数段
       if (null != productReviewSearchCriteria.getScoreSpan()) {
           switch(productReviewSearchCriteria.getScoreSpan().shortValue()){
        	   case ProductReviewSearchCriteria.SCORE_SPAN_1: hqlsb.append(" and r.score >= 0 and r.score < 2");break;
        	   case ProductReviewSearchCriteria.SCORE_SPAN_2: hqlsb.append(" and r.score >= 2 and r.score < 3");break;
        	   case ProductReviewSearchCriteria.SCORE_SPAN_3: hqlsb.append(" and r.score >= 3 and r.score < 4");break;
        	   case ProductReviewSearchCriteria.SCORE_SPAN_4: hqlsb.append(" and r.score >= 4 and r.score < 5");break;
        	   case ProductReviewSearchCriteria.SCORE_SPAN_5: hqlsb.append(" and r.score = 5");break;
           	   default:break;
           }
       }
       //-end:分数段
       
       //-start:好中差评
       if (null != productReviewSearchCriteria.getScoreLevel()) {
           switch(productReviewSearchCriteria.getScoreLevel().shortValue()){
        	   case ProductReviewSearchCriteria.SCORE_LEVEL_GOOD: hqlsb.append(" and r.score >= 4 and r.score <= 5");break;
        	   case ProductReviewSearchCriteria.SCORE_LEVEL_MID: hqlsb.append(" and r.score >= 3 and r.score < 4");break;
        	   case ProductReviewSearchCriteria.SCORE_LEVEL_BAD: hqlsb.append(" and r.score >= 0 and r.score < 3");break;
           	   default:break;
           }
       }
       //-end:好中差评
       
       //start:order by
       if (null != productReviewSearchCriteria.getOrderby()) {
           hqlsb.append(" order by ").append(getSortByString(productReviewSearchCriteria.getOrderby()));
       }else{
           hqlsb.append(" order by r.productReviewId desc,r.createTime desc");
       }
       //end:order by
       //end:构建HQL语句
       searchCriteria.setHql(hqlsb.toString());
       searchCriteria.removeParamValues();
       for (Object object : paramList) {
           searchCriteria.addParamValue(object);
       }
       return searchCriteria;
	}
	
   /*
    * (non-Javadoc)
    * @see com.probiz.estore.product.dao.ProductReviewDao#searchByCriteria(com.probiz.estore.product.model.ProductReviewSearchCriteria, com.probiz.estore.core.search.SearchCriteria)
    */
   public List<ProductReview> searchByCriteria(ProductReviewSearchCriteria productReviewSearchCriteria, SearchCriteria searchCriteria) {
       List<ProductReview> productReviewList =  searchByCriteria(getSearchCriteria(productReviewSearchCriteria, searchCriteria));
       return productReviewList;
   }

   /**
    * 根据orderby获得 hql语句
    * @param orderby 排序id
    * @return hql语句
    */
   private String getSortByString(short orderby) {
       String orderByString;
       switch (orderby) {
           case 1:
               orderByString = "r.productReviewId desc,r.createTime desc";  //最新
               break;
           case 2:
               orderByString = "r.usefulCount desc,r.productReviewId desc";  //有用
               break;
           case 3:
               orderByString = "convert_gbk(r.subject) asc";
               break;
           case 4:
               orderByString = "convert_gbk(r.subject) desc";
               break;
           case 5:
        	   orderByString = "convert_gbk(r.product.productName) asc";
        	   break;
           case 6:
        	   orderByString = "convert_gbk(r.product.productName) desc";
        	   break;
           case 7:
        	   orderByString = "r.score asc";
        	   break;
           case 8:
        	   orderByString = "r.score desc";
        	   break;
           case 9:
        	   orderByString = "r.createTime asc";
        	   break;
           case 10:
        	   orderByString = "r.createTime desc";
        	   break;
           default:
               orderByString = "r.sortOrder asc,r.productReviewId desc,r.createTime desc";
               break;
       }
       return orderByString;
   }
   /*
    * (non-Javadoc)
    * @see com.probiz.estore.product.dao.ProductReviewDao#getReplyCountByProductReviewId(java.lang.Integer)
    */
   public Long getReplyCountByProductReviewId(Integer productReviewId){
	   return this.countByHql("from ProductReview r where r.parent.productReviewId = ?", productReviewId);
   }
   /*
    * (non-Javadoc)
    * @see com.probiz.estore.product.dao.ProductReviewDao#getOnShowReplyCountByProductReviewId(java.lang.Integer)
    */
   public Long getOnShowReplyCountByProductReviewId(Integer productReviewId){
	   return this.countByHql("from ProductReview r where r.parent.productReviewId = ? and r.status=1", productReviewId);
   }
   /*
    * (non-Javadoc)
    * @see com.probiz.estore.product.dao.ProductReviewDao#getReviewCountByProductId(java.lang.Integer)
    */
   public Long getReviewCountByProductId(Integer productId){
	   return this.countByHql("from ProductReview r where r.product.productId = ? and r.parent is null", productId);
   }
   /*
    * (non-Javadoc)
    * @see com.probiz.estore.product.dao.ProductReviewDao#getOnShowReviewCountByProductId(java.lang.Integer)
    */
   public Long getOnShowReviewCountByProductId(Integer productId){
	   return this.countByHql("from ProductReview r where r.product.productId = ? and  r.parent is null and r.status=1", productId);
   }
   /*
    * (non-Javadoc)
    * @see com.probiz.estore.product.dao.ProductReviewDao#getReviewScoreByProductId(java.lang.Integer)
    */
   public BigDecimal getReviewScoreByProductId(final Integer productId){
	   Double score = (Double)getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery("select avg(r.score) from ProductReview r where r.product.productId = "+productId+" and r.status=1 and  r.parent is null");
				return query.uniqueResult();
			}

		});
	   if(score != null){
		   return new BigDecimal(score);
	   }else{
		   return new BigDecimal("0");
	   }
   }
   
   /*
    * (non-Javadoc)
    * @see com.probiz.estore.product.dao.ProductReviewDao#getOnShowReplyListByReviewId(java.lang.Integer)
    */
	public List<ProductReview> getOnShowReplyListByReviewId(Integer productReviewId){
		return this.findByHql("from ProductReview pr where pr.parent.productReviewId =? and pr.status = 1 order by pr.createTime asc", productReviewId);
	}
	/*
	 * (non-Javadoc)
	 * @see com.probiz.estore.product.dao.ProductReviewDao#countProductReviewTotal()
	 */
	public Long countProductReviewTotal(){
		return this.countByHql("from ProductReview pr where pr.parent is null");
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.probiz.estore.product.dao.ProductReviewDao#getPreviousCustomerIdListByProductIdBySize(java.lang.Integer, java.lang.Integer)
	 */
	public List<Integer> getPreviousCustomerIdListByProductIdBySize(final Integer productId,final Integer size){
		List list;
		list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery("select distinct(pr.customer.appuserId) from ProductReview pr where pr.product.productId = :productId and pr.customer is not null");
				query.setInteger("productId", productId);
				query.setMaxResults(size);
				List<Integer> rows = (List<Integer>) query.list();
				return rows;
			}

		});
		return list;
	}
}
